package com.jeecg.sms.job;

import java.io.UnsupportedEncodingException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.jeecgframework.core.common.exception.BusinessException;
import org.jeecgframework.core.util.DynamicDBUtil;
import org.jeecgframework.core.util.HttpRequest;
import org.jeecgframework.core.util.MutiLangUtil;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.web.system.pojo.base.DictEntity;
import org.jeecgframework.web.system.pojo.base.TSType;
import org.jeecgframework.web.system.service.SystemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jeecg.sms.entity.RptSmsSendEntity;
import com.jeecg.sms.service.RptSmsSendServiceI;

@Service("messageTask")
public class MessageJob{

	private final static Logger  logger = Logger.getLogger(MessageJob.class);
	@Autowired
	private RptSmsSendServiceI rptSmsSendService;
	@Autowired
	private SystemService systemService;
		
	/**
	 * 生成生产日报短信
	 * @return 
	 */
	public void run() {
		//org.jeecgframework.core.util.LogUtil.info("===================消息中间件定时任务开始===================");

		logger.info("===================短信生成定时任务开始===================");
		//c1:昨日时间:c2:起降架次,c3:进港架次,c4:出港架次,c5:旅客,c6:进港旅客,c7:出港旅客,c8:机场物流货邮,c81:进港货邮,c82:出港货邮,
		 //c9:国际旅客,c10:进港国际,c11:出港国际,c12:同比架次增长,	c13:同比旅客增长,c14:同比货邮增长,c15:同比国际旅客,c16:当月月初,c17:昨日,
		 //c18:本月航班,c19:本月旅客,c20:本月货邮,c21:本月国际旅客,c22:日平均航班,c23:日平均旅客,c24:日平均货邮,c25:日平均国际旅客,
		 //c26:同比增长航班,c27:同比增长旅客,c28:同比增长货邮,c29:同比增长国际旅客,
		 //c30:当年年初,c31:昨日,c32:本年航班,c33:本年日平均航班,c34:航班年累同比增长,c35:完成年航班计划,
		 //c36:本年旅客,c37:本年日平均旅客,c38:旅客年累同比增长,c39:完成年旅客计划,c40:本年货邮,c41:本年日平均货邮,c42:货邮年累同比增长,c43:完成年货邮计划,
		 //c44:本年国际旅客,c45:本年日平均国际旅客,c46:国际旅客年累同比增长,c47:国际旅客占总旅客的比重
		String sql="select to_char(to_date(t.rq,'yyyymmdd'),'yyyy\"年\"mm\"月\"dd\"日\"') as c1,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%') as c2,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and io='A') as c3,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and io='D') as c4,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0) as c5,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and d.FLIO='I') as c6,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and d.FLIO='O') as c7,"+
				"(select round((sum(d.FREW)+sum(d.POSW))/1000,2) from flt_all_data d where d.qjrq = t.rq and d.state = 0 and d.PROXY = '机场代理' AND d.ADNO = 0) as c8,"+
				"(select round((sum(d.frew)+sum(d.POSW))/1000,2) from flt_all_data d where qjrq=t.rq and d.state = 0 and d.PROXY = '机场代理' AND d.ADNO = 0 and d.FLIO='I') as c81,"+
				"(select round((sum(d.frew)+sum(d.POSW))/1000,2) from flt_all_data d where qjrq=t.rq and d.state = 0 and d.PROXY = '机场代理' AND d.ADNO = 0 and d.FLIO='O') as c82,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) as c9,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG') and d.FLIO='I') as c10,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG') and d.FLIO='O') as c11,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select count(1) n from flt_cd_i_aomip a where a.state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%') th,(select (a.c+b.c) as n from (select sum(cont) as c from flt_all_data where state =0 and qjrq=to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd')) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq=to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd')) b) la) as c12,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq=t.rq and d.state = 0) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq=to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.state = 0) la) as c13,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq=t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) th,(select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq=to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) la) as c14,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq=t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq=to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) la) as c15,"+
				"to_char(trunc(to_date(t.rq,'yyyymmdd'), 'mm'),'yyyy\"年\"mm\"月\"dd\"日\"') as c16,to_char(to_date(t.rq,'yyyymmdd'),'yyyy\"年\"mm\"月\"dd\"日\"') as c17,"+
				"(select  round((a.c+b.c)/10000,4) from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bmd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bmd and t.rq) b) as c18,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq between bmd and t.rq and d.state = 0) as c19,"+
				"(select round((sum(d.frew)+sum(d.POSW))/10000000,4) from flt_all_data d where qjrq between bmd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) as c20,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq between bmd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) as c21,"+
				"(select  round((a.c+b.c)/(to_date(t.rq,'yyyymmdd')-to_date(t.bmd,'yyyymmdd')+1),0) from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bmd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bmd and t.rq) b) as c22,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/((to_date(t.rq,'yyyymmdd')-to_date(t.bmd,'yyyymmdd')+1)*10000),4) from flt_all_data d where qjrq between bmd and t.rq and d.state = 0) as c23,"+
				"(select round((sum(d.frew)+sum(d.POSW))/((to_date(t.rq,'yyyymmdd')-to_date(t.bmd,'yyyymmdd')+1)*1000),2) from flt_all_data d where qjrq between bmd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) as c24,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/((to_date(t.rq,'yyyymmdd')-to_date(t.bmd,'yyyymmdd')+1)*10000),4) from flt_all_data d where qjrq between bmd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) as c25,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (a.c+b.c) as n  from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bmd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bmd and t.rq) b) th,(select (a.c+b.c) as n from (select sum(cont) as c from flt_all_data where state =0 and qjrq between lbmd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd')) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between lbmd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd')) b) la) as c26,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bmd and t.rq and d.state = 0) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between lbmd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.state = 0) la) as c27,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq between bmd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) th,(select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq between lbmd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) la) as c28,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bmd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between lbmd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) la) as c29,"+
				"to_char(trunc(to_date(t.rq,'yyyymmdd'), 'yy'),'yyyy\"年\"mm\"月\"dd\"日\"') as c30,to_char(to_date(t.rq,'yyyymmdd'),'yyyy\"年\"mm\"月\"dd\"日\"') as c31,"+
				"(select  round((a.c+b.c)/10000,4) from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bymd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bymd and t.rq) b) as c32,"+
				"(select  round((a.c+b.c)/(to_date(t.rq,'yyyymmdd')-to_date(t.bymd,'yyyymmdd')+1),0) from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bymd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bymd and t.rq) b) as c33,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (a.c+b.c) as n  from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bymd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bymd and t.rq) b) th,(select (a.c+b.c) as n from (select sum(cont) as c from flt_all_data where state =0 and qjrq between lbymd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd')) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between lbymd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd')) b) la) as c34,"+
				"(select round(th.n*100/la.n,2) from (select (a.c+b.c) as n  from (select sum(cont) as c from flt_all_data where state =0 and qjrq between bymd and t.rq) a, (select COALESCE(sum(qfjc),0)+COALESCE(sum(jljc),0) as c from tb_cd_whx  where rq between bymd and t.rq) b) th,(select sum(d.gf_qjjc) as n from BI_SB_GF_JCTTLNDJHB d where d.gf_nf=TO_CHAR(to_date(t.rq,'yyyymmdd'),'yyyy')) la) as c35,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq between bymd and t.rq and d.state = 0) as c36,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/((to_date(t.rq,'yyyymmdd')-to_date(t.bymd,'yyyymmdd')+1)*10000),4) from flt_all_data d where qjrq between bymd and t.rq and d.state = 0) as c37,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bymd and t.rq and d.state = 0) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between lbymd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.state = 0) la) as c38,"+
				"(select round(th.n*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bymd and t.rq and d.state = 0) th,(select sum(d.gf_lk) as n from BI_SB_GF_JCTTLNDJHB d where d.gf_nf=TO_CHAR(to_date(t.rq,'yyyymmdd'),'yyyy')) la) as c39,"+
				"(select round((sum(d.frew)+sum(d.POSW))/10000000,4) from flt_all_data d where qjrq between bymd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) as c40,"+
				"(select round((sum(d.frew)+sum(d.POSW))/((to_date(t.rq,'yyyymmdd')-to_date(t.bymd,'yyyymmdd')+1)*1000),2) from flt_all_data d where qjrq between bymd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) as c41,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq between bymd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) th,(select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq between lbymd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) la) as c42,"+
				"(select round(th.n*0.1/la.n,2) from (select (sum(d.frew)+sum(d.POSW)) as n from flt_all_data d where qjrq between bymd and t.rq and d.PROXY = '机场代理' AND d.ADNO = 0 and d.state = 0) th,(select sum(gf_hw)+sum(gf_yj) as n from BI_SB_GF_JCTTLNDJHB d where d.gf_nf=TO_CHAR(to_date(t.rq,'yyyymmdd'),'yyyy')) la) as c43,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq between bymd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) as c44,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/((to_date(t.rq,'yyyymmdd')-to_date(t.bymd,'yyyymmdd')+1)*10000),4) from flt_all_data d where qjrq between bymd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) as c45,"+
				"(select round((th.n-la.n)*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bymd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between lbymd and to_char(add_months(to_date(t.rq,'yyyymmdd'),-12),'yyyymmdd') and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) la) as c46,"+
				"(select round(th.n*100/la.n,2) from (select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bymd and t.rq and d.state = 0 and (d.flrt_type = 'INT' or d.flrt_type = 'REG')) th,(select (sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO)) as n from flt_all_data d where qjrq between bymd and t.rq and d.state = 0) la) as c47,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and awcd not in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ')) as C50,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and awcd not in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and a.IO='A') as C51,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and awcd not in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and a.IO='D') as C52,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and awcd not in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ')) as c55,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and awcd not in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and d.FLIO='I') as c56,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and awcd not in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and d.FLIO='O') as c57,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and awcd in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ')) as C60,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and awcd in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and a.IO='A') as C61,"+
				"(select count(1) from flt_cd_i_aomip a where state=0 and a.sjsj like to_char(to_date(t.rq,'yyyymmdd'),'yyyy-mm-dd')||'%' and awcd in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and a.IO='D') as C62,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and awcd in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ')) as c65,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and awcd in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and d.FLIO='I') as c66,"+
				"(select round((sum(d.ADNO)+sum(d.CHNO)+sum(d.PASS_ADNO)+sum(d.PASS_CDNO))/10000,4) from flt_all_data d where qjrq=t.rq and d.state = 0 and awcd in ('KQ','KE','GA','VN','AF','CI','SV','SU','JL','TG','SQ','OQ','MF','NS','3U','CZ') and d.FLIO='O') as c67"+
				" from (select to_char(sysdate-1,'yyyymmdd') as rq,to_char(trunc(to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd'), 'mm'),'yyyymmdd') as bmd,to_char(trunc(to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd'), 'yy'),'yyyymmdd') as bymd,to_char(trunc(to_date(to_char(add_months(sysdate,-12)-1,'yyyymmdd'),'yyyymmdd'), 'mm'),'yyyymmdd') as lbmd,to_char(trunc(to_date(to_char(add_months(sysdate,-12)-1,'yyyymmdd'),'yyyymmdd'), 'yy'),'yyyymmdd') as lbymd from dual) t";
		List<Map<String,Object>> dataList =  DynamicDBUtil.findList("production_db", sql);
		sql = "select to_char(sysdate-1,'yyyy\"年\"mm\"月\"dd\"日\"') as rq,"+
				"to_char(trunc(sysdate-1, 'mm'),'yyyy\"年\"mm\"月\"dd\"日\"') as mrq,"+
				"to_char(trunc(sysdate-1, 'yy'),'yyyy\"年\"mm\"月\"dd\"日\"') as yrq,"+
				"(select case when sum(A.MAXP)=0 then 0 when sum(A.MAXP)>0 then ROUND((SUM(A.ADNO)+SUM(A.CHNO)+SUM(A.PASS_ADNO)+SUM(a.PASS_CDNO))*100/(sum(A.MAXP)),2) end  from flt_all_data A,"+
				" TB_CD_HBXZ_CODE B,TB_CD_CYR_CODE C"+
				" where a.qjrq = to_char(sysdate-1,'yyyymmdd')"+
				" AND A.FLTK=B.HBXZ AND A.AWCD=C.CYR AND A.FLIO='O'"+
				" AND B.XZFL2 IN  ('正班','加班')) as dkzl,(select case when sum(A.MAXP)=0 then 0 when sum(A.MAXP)>0 then ROUND((SUM(A.ADNO)+SUM(A.CHNO)+SUM(A.PASS_ADNO)+SUM(a.PASS_CDNO))*100/(sum(A.MAXP)),2) end  from flt_all_data A,"+
				" TB_CD_HBXZ_CODE B,TB_CD_CYR_CODE C"+
				" where a.qjrq between to_char(trunc(sysdate-1,'mm'),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd')"+
				" AND A.FLTK=B.HBXZ AND A.AWCD=C.CYR AND A.FLIO='O'"+
				" AND B.XZFL2 IN  ('正班','加班')) as mkzl,(select case when sum(A.MAXP)=0 then 0 when sum(A.MAXP)>0 then ROUND((SUM(A.ADNO)+SUM(A.CHNO)+SUM(A.PASS_ADNO)+SUM(a.PASS_CDNO))*100/(sum(A.MAXP)),2) end  from flt_all_data A,"+
				" TB_CD_HBXZ_CODE B,TB_CD_CYR_CODE C"+
				" where a.qjrq between to_char(trunc(sysdate-1,'yy'),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd')"+
				" AND A.FLTK=B.HBXZ AND A.AWCD=C.CYR AND A.FLIO='O' AND B.XZFL2 IN  ('正班','加班')) as ykzl"+
				" from dual";
		List<Map<String,Object>> data2List =  DynamicDBUtil.findList("production_db", sql);
		String p1Str="";
		String p2Str="";
		String p3Str="";
		String p4Str="";
		String p5Str="";
		if(dataList.size()>0)
		{
			p1Str= "数据快报：\r\n一、生产日报\r\n"+dataList.get(0).get("C1")+"航班起降架"+dataList.get(0).get("C2")+"架次(进港"+dataList.get(0).get("C3")+"，出港"+dataList.get(0).get("C4")+")、旅客"+
					dataList.get(0).get("C5")+"万人次（进港"+dataList.get(0).get("C6")+"、出港"+dataList.get(0).get("C7")+"）、"+
					"机场物流货邮"+dataList.get(0).get("C8")+"吨（进港"+dataList.get(0).get("C81")+"、出港"+dataList.get(0).get("C82")+"）"+
					"和国际旅客"+dataList.get(0).get("C9")+"万人次（进港"+dataList.get(0).get("C10")+"，出港"+dataList.get(0).get("C11")+"），同比分别增长"+dataList.get(0).get("C12")+"%、"+
					dataList.get(0).get("C13")+"%"+
					"、"+dataList.get(0).get("C14")+"%"+
					"和"+dataList.get(0).get("C15")+"%。\r\n";
			p2Str = "二、累计数据\r\n"+dataList.get(0).get("C16")+"至"+dataList.get(0).get("C17")+
					"航班、旅客"+
					"、机场物流货邮"+
					"和国际旅客分别是"+dataList.get(0).get("C18")+"万架次、"+dataList.get(0).get("C19")+"万人次"+
					"、"+dataList.get(0).get("C20")+"万吨"+
					"和"+dataList.get(0).get("C21")+
					"万人次，日平均分别是"+dataList.get(0).get("C22")+"架次、"+dataList.get(0).get("C23")+"万人次"+
					"、"+dataList.get(0).get("C24")+"吨"+
					"和"+dataList.get(0).get("C25")+
					"万人次，同比分别增长"+dataList.get(0).get("C26")+"%、"+dataList.get(0).get("C27")+"%"+
					"、"+dataList.get(0).get("C28")+"%"+
					"和"+dataList.get(0).get("C29")+"%。\r\n"+
					dataList.get(0).get("C30")+"至"+dataList.get(0).get("C31")+"航班起降"+dataList.get(0).get("C32")+"万架次，日平均"+dataList.get(0).get("C33")+"架次，同比增长"+dataList.get(0).get("C34")+"%，完成计划的"+
					dataList.get(0).get("C35")+"%；旅客"+dataList.get(0).get("C36")+"万人次，日平均"+dataList.get(0).get("C37")+"万人次，同比增长"+dataList.get(0).get("C38")+"%，完成计划的"+
					dataList.get(0).get("C39")+"%；"+
					"机场物流货邮"+dataList.get(0).get("C40")+"万吨，日平均"+dataList.get(0).get("C41")+"吨，同比增长"+dataList.get(0).get("C42")+"%；"+
					"国际旅客"+dataList.get(0).get("C44")+"万人次，日平均"+dataList.get(0).get("C45")+"万人次，同比增长"+dataList.get(0).get("C46")+
					"%，占总旅客的比重为"+dataList.get(0).get("C47")+"%。";
			if(data2List.size()>0)
			{
				p5Str = "客座率\r\n"+data2List.get(0).get("RQ")+"出港平均客座率"+data2List.get(0).get("DKZL")+"%\r\n";
				p3Str = "\r\n客座率\r\n"+
						data2List.get(0).get("MRQ")+"至"+data2List.get(0).get("RQ")+"出港平均客座率"+data2List.get(0).get("MKZL")+"%\r\n"+
						data2List.get(0).get("YRQ")+"至"+data2List.get(0).get("RQ")+"出港平均客座率"+data2List.get(0).get("YKZL")+"%";
			}
			p1Str +="T1航站楼：\r\n"+
					""+dataList.get(0).get("C1")+"航班起降架"+dataList.get(0).get("C50")+"架次(进港"+dataList.get(0).get("C51")+"，出港"+dataList.get(0).get("C52")+")、旅客"+dataList.get(0).get("C55")+"万人次（进港"+dataList.get(0).get("C56")+"、出港"+dataList.get(0).get("C57")+"）\r\n"+
					"T2航站楼：\r\n"+
					""+dataList.get(0).get("C1")+"航班起降架"+dataList.get(0).get("C60")+"架次(进港"+dataList.get(0).get("C61")+"，出港"+dataList.get(0).get("C62")+")、旅客"+dataList.get(0).get("C65")+"万人次（进港"+dataList.get(0).get("C66")+"、出港"+dataList.get(0).get("C67")+"）\r\n";

			p4Str = "（白云机场运控中心快报）";
			//发送信息到接口
			List<TSType> typeList = ResourceUtil.allTypes.get("0000");
			String phones="";
			int index = 0;
			if (typeList != null && !typeList.isEmpty()) {
				for (TSType type : typeList) {
					if (index>0) phones +=",";
					phones +=  type.getTypecode();
					index++;
				}
			}
			List<TSType> typeList2 = ResourceUtil.allTypes.get("0001");
			String url="";
			String password="";
			if (typeList != null && !typeList.isEmpty()) {
				for (TSType type : typeList2) {
					if (type.getTypename().equals("url")) 
						url = type.getTypecode();
					if (type.getTypename().equals("password")) 
						password=type.getTypecode();
				}
			}
			String content=p1Str+p5Str;
			String content2=p2Str+p3Str+p4Str;
			try {
				content =java.net.URLEncoder.encode(content,"utf-8");
				content2 =java.net.URLEncoder.encode(content2,"utf-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			String param ="password="+password+"&text="+content+"&recipient="+phones+"&encoding=U";
			logger.info("发送第一段===================");
			String result =HttpRequest.sendGet(url, param);
			logger.info(result);
			String param2 ="password="+password+"&text="+content2+"&recipient="+phones+"&encoding=U";
			logger.info("发送第二段===================");
			String result2 =HttpRequest.sendGet(url, param2);
			logger.info(result2);
		}
		else
		{
			logger.info("没有查询到当日生产数据===================");
		}
		logger.info("===================短信生成定时任务结束===================");
	}
	public static void main(String[] args){
		MessageJob mj=new MessageJob();
		mj.run();
	}
}
